/**
 * Created by Administrator on 2017/10/16.
 */
var common = require('../../common');
var pool = require('../index');
var mysql = require('mysql');
var ItbBaseModel = require('./ItbBaseModel');


// Schema 结构
var ItbExpTrackInfoModel = function(obj){
    ItbBaseModel.call(this,obj);

    //`ex_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '物流信息id',
    //`source` int(2) NOT NULL DEFAULT '10' COMMENT '信息来源：0未知，10示界港，20阿里云',
    //`exc_id` bigint(20) DEFAULT NULL COMMENT '快递公司id',
    //`ex_tracking_no` varchar(50) NOT NULL DEFAULT '' COMMENT '物流单号',
    //`trade_type` int(3) DEFAULT NULL COMMENT '类型:10商品订单,20发票,30购买模版,40虚拟商品(如vip),50秒杀订单',
    //`trade_obj_id` bigint(20) DEFAULT NULL COMMENT '订单id',
    //`from_addr_id` bigint(20) DEFAULT NULL COMMENT '发件人地址',
    //`to_addr_id` bigint(20) DEFAULT NULL COMMENT '收件人地址',
    //`user_id` bigint(20) DEFAULT NULL COMMENT '用户id',
    //`state` int(2) NOT NULL DEFAULT '0' COMMENT '当前快递状态：0未知，1揽件中，2在途中，3派件中，4已签收，5派件失败/拒签，6疑难/问题件，7退回',
    //`data` mediumtext COMMENT '物流信息json字符串',
    //`desc` varchar(100) DEFAULT NULL COMMENT '备注',
    //`create_time` datetime NOT NULL,
    //`update_time` datetime NOT NULL,
    //`update_by` varchar(50) DEFAULT '',
    //`del_flg` int(1) NOT NULL DEFAULT '0',
};

ItbExpTrackInfoModel.prototype = new ItbBaseModel();

// 取得表名
ItbExpTrackInfoModel.prototype.getTableName = function(){
    return 'itb_exp_track_info';
};
// 取得查询语句中where 后面匹配主键的条件（需要各派生类进行重载）
ItbExpTrackInfoModel.prototype.getWhereConditionOfKeyEqual = function(){
    return 'ex_id='+this.ex_id;
};

//------------------------------------------------------------------
/**
 * 获取需要处理的物流单号信息
 * @param callback
 */
ItbExpTrackInfoModel.prototype.getTodoExpTrackItems=function(callback){
    var THIS = this;
    var tableName = this.getTableName();

    // 从mysql中查询
    pool.pool.getConnection(function(err, connection) {
        var sql =
            ' SELECT t2.ex_id, t2.state, x.*,t3.cd '+
            ' FROM ( '+
            '     SELECT DISTINCT 10 AS trade_type, o.order_id, '+
            '     d.exc_id, d.tracking_no, '+
            '     o.addr_id AS to_addr_id, o.user_id '+
            '     FROM ITB_TRADE_ORDER o, ITB_TRADE_ORDER_DETAIL d '+
            '     WHERE o.del_flg=0 AND o.state in (30) '+
            '     AND d.del_flg=0 AND d.order_id=o.order_id '+
            "     AND LENGTH(TRIM(IFNULL(d.tracking_no,'')))>1 "+
            ' UNION '+
            '     SELECT t.trade_type, t.trade_obj_id AS order_id, '+
            '     t.exc_id, t.ex_tracking_no AS tracking_no, '+
            '     t.to_addr_id, t.user_id '+
            '     FROM ITB_EXP_TRACK_INFO t '+
            '     WHERE t.del_flg=0 AND t.state IN (0,1,2,3) '+
            "     AND LENGTH(TRIM(IFNULL(t.ex_tracking_no,'')))>1 "+
            ' ) x '+
            ' LEFT JOIN ITB_EXP_TRACK_INFO t2 '+
            ' ON t2.del_flg=0 '+
            ' AND (t2.exc_id=x.exc_id OR (t2.exc_id is null and x.exc_id is null)) '+
            ' AND t2.ex_tracking_no=x.tracking_no ' +
            'LEFT JOIN itb_exp_com_info t3 ' +
            'ON t3.del_flg=0 AND t3.exc_id = x.exc_id ' +
            'ORDER by IFNULL(t2.ex_id,0) ';

        console.log(sql);

        // Use the connection
        connection.query(sql, function(error, items) {
            // And done with the connection.
            connection.release();

            // 回调
            callback(error, items);
        });

    });
};

//根据orderId(订单id)获取对应订单的物流信息
/*
* 参数: orderId  订单唯一标识
* 参数: userId   用户唯一标识
* 参数：callback 回调，参数如下：
       * error:错误
       * result:多行数据
* */
ItbExpTrackInfoModel.prototype.getLogisticsInfo = function(userId,orderId,callback){
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbExpTrackInfoModel.getLogisticsInfo Err:'+err);
        }

        var sql = "SELECT p.* " +
            " FROM itb_exp_track_info p " +
            " WHERE trade_type = 10 " +
            " AND user_id = " + userId +
            " AND trade_obj_id = " + orderId +
            " AND del_flg = 0";
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            callback(error, result);
        });
    });
};


//通过exc_id获取对应的物流公司信息
/*
 * 参数: excId  物流公司唯一标识
 * 参数：callback 回调，参数如下：
 * error:错误
 * result:多行数据
 * */
ItbExpTrackInfoModel.prototype.getLogisticsCompanyInfo = function(excId,callback){
    pool.pool.getConnection(function(err, connection) {
        if (err) {
            console.log('ItbExpTrackInfoModel.getLogisticsCompanyInfo Err:'+err);
        }
        var sql = "SELECT p.* " +
            " FROM itb_exp_com_info p " +
            " WHERE exc_id = " + excId +
            " AND del_flg = 0";
        console.log(sql);
        connection.query(sql, function(error, result) {
            // And done with the connection.
            connection.release();
            callback(error, result);
        });
    });
};

//-------------------------------------------------------------------------
/**
 *   author- 李琦
 *   获取本地DB 未完成的物流订单信息
 *   (state!=4 and state!=5)
 *   4表示已签收 5表示拒收
 */
ItbExpTrackInfoModel.prototype.getUncompletedExpInfo = function(callback){
    pool.pool.getConnection(function(err,connection){
        if(err){
            console.log('ItbExpTrackInfoModel getUncompletedExpInfo err:'+err);
        }
        var sql = 'SELECT t.ex_id,t.exc_id,t.ex_tracking_no,a.cd' +
            ' FROM itb_exp_track_info t' +
            ' LEFT JOIN itb_exp_com_info a ON t.exc_id = a.exc_id' +
            ' where t.del_flg=0 AND a.del_flg=0 AND t.state <> 4 AND t.state <> 5';
        console.log(sql);
        connection.query(sql,function(error,result){ //连接mysql查询数据
            connection.release();// down with the connection
            callback(error,result);
        });
    });
};

//-------------------------------------------------------------------------
/** 更新本地DB的物流信息 ---李琦
 *  更新itb_exp_track_info 的 data
 *  参数：ex_id 物流ID
 *  参数：data 物流信息JSON格式 updExpTrackInfoData
 *  参数：state 物流的状态
 */
ItbExpTrackInfoModel.prototype.updExpTrackInfoData = function(ex_id,state,data,update_by,callback){
    pool.pool.getConnection(function(err,connection){
        if(err){
            console.log('ItbExpTrackInfoModel/updExpTrackInfoData connection err:'+err);
        }
        var sql = 'UPDATE itb_exp_track_info t '+
            'SET t.update_time = SYSDATE(),t.update_by="'+update_by+'",t.state ='+state+' ,' +
            't.`data` =\''+data+'\' WHERE t.del_flg = 0 AND t.ex_id ='+ex_id+'';
        console.log(sql);
        connection.query(sql,function(error,result){ //连接mysql查询数据
            connection.release();// down with the connection
            if(error){
                console.log("ItbExpTrackInfoModel/updExpTrackInfoData Error:"+error);
                callback(error);
            }else{
                callback(null);//fj
            }
        });
    });
};

/**@Author--李琦
 * 插入物流信息到本地DB
 * @type {Function}
 */
ItbExpTrackInfoModel.prototype.insertItbExpTrackInfo = function(info,state,data,callback){
    pool.pool.getConnection(function(err,connection){
        if(err){
            console.log('ItbExpTrackInfoModel/insertItbExpTrackInfo connection err:'+err);
        }
        var sql = 'INSERT INTO itb_exp_track_info(`source`,exc_id,ex_tracking_no,trade_type,trade_obj_id,' +
            'to_addr_id,user_id,state,`data`,create_time,update_time,update_by,del_flg) VALUES ' +
            '(20,'+info.exc_id+','+info.tracking_no+','+info.trade_type+','+info.order_id+',' +
            ''+info.to_addr_id+','+info.user_id+','+state+',\''+data+'\',SYSDATE(),SYSDATE(),"'+info.update_by+'",0);';
        console.log(sql);
        connection.query(sql,function(error,result){ //连接mysql查询数据
            connection.release();// down with the connection
            if(error){
                console.log("ItbExpTrackInfoModel/insertItbExpTrackInfo Error:"+error);
                callback(error);
            }else{
                callback(null);//fj
            }
        });
    });
};


// 导出
module.exports = ItbExpTrackInfoModel;